package webExperiment;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * version 1.6
 * 
 * 1.6 new file format
 */
public class Database {

    public static class PHPDetails {
	public String path = "";
	public String updateDataScript = "";
	public String newDataScript = "";
	public String sendMailScript = "";

	public PHPDetails(String path, String newDataScript,
		String updateDataScript, String sendMailScript) {
	    this.path = path;
	    this.newDataScript = newDataScript;
	    this.updateDataScript = updateDataScript;
	    this.sendMailScript = sendMailScript;
	}
    }

    public final static short PHP_MODE = 1;
    public final static short SQL_MODE = 2;
    public final static short FILE_MODE = 3;
    public final static short NO_MODE = 4;
    public final static int ERROR = -99;

    private static String prepareFromSQLUpdate(String data) { // returns array
	// of two strings
	String rtn = "";
	String[] arr = null;
	arr = data.split("&");
	for (int x = 1; x < arr.length; x++) { // just after the first &
	    String tmp[] = arr[x].split("=");
	    if (tmp.length >= 2) {
		rtn = rtn + "`" + tmp[0] + "` = '" + tmp[1] + "',";
	    }
	    if (tmp.length == 1) {
		rtn = rtn + "`" + tmp[0] + "` = '',";
	    }
	}
	return SQLHandler.trimComma(rtn);
    }

    private static String runPhpScript(String script) {
	// System.out.println(script);// debug
	String feedback = "";
	try {
	    URL url = new URL(script);
	    BufferedReader in = new BufferedReader(new InputStreamReader(url
		    .openStream()));
	    feedback = in.readLine(); // get first line
	    while (in.readLine() != null) {/* read everything out */
	    }
	    in.close();
	} catch (MalformedURLException e) {
	} catch (IOException e) {
	}
	return feedback;
    }

    private static ArrayList<String> separateVarFromValsForSQL(String data) { // returns
	// array
	// of
	// two
	// strings
	String vars = "";
	String vals = "";
	String[] arr = null;
	arr = data.split("&");
	for (int x = 1; x < arr.length; x++) { // just after the first &
	    String tmp[] = arr[x].split("=");
	    if (tmp.length >= 2) {
		vars = vars + "`" + tmp[0] + "`,";
		vals = vals + "'" + tmp[1] + "',";
	    }
	}

	ArrayList<String> rtn = new ArrayList<String>();
	rtn.add(new String(SQLHandler.trimComma(vars)));
	rtn.add(new String(SQLHandler.trimComma(vals)));
	return rtn;
    }

    private String appletName;
    private String tag;
    private String sqlIDName;
    private String fileName;
    private String dataLine;
    private int lastSQLID;
    private PHPDetails phpDetails;

    private short mode = NO_MODE;

    private SQLHandler sqlHdl = null;

    // private SQLHandler.ConnectionDetails sqlDetails;
    private String sqlTable;

    public Database(String tag, String appletName) {
	this.appletName = appletName;
	this.tag = tag; // need for filename
    }

    public String addData(String data) throws SQLException {
	return processData(data, false);
    }

    public boolean doesTableExist() throws SQLException {
	return sqlHdl.doesTableExist(sqlTable);
    }

    public void ensureTableExist(URL SQLfile) throws IOException, SQLException {
	if (!doesTableExist()) {
	    sqlHdl.processSQLFile(SQLfile);
	}
    }

    public String getAppletName() {
	return appletName;
    }

    public int getMode() {
	return mode;
    }

    public int getSQLID() {
	return lastSQLID;
    }

    /*
     * MAKE SEND public String sendDataEmail() { if
     * (phpDetails.path.equals(NOPHP)) { String script =
     * phpDetails.path+phpDetails.sendMailScript +"?"; script =
     * script+sqlIdxVarName+"="+phpIdx; String rtn= runScript(script); if
     * (rtn.substring(0, 2).equals("ok")) { return rtn.substring(3); } else //
     * error return "Sql error, please contact the administrator"; } return
     * "ok"; }
     */

    /*
     * data-string Syntax: &varname=data
     */

    public String getTag() {
	return tag;
    }

    public int newEntry(String data) throws SQLException { // return pID if
	// error
	String rtn = processData(data, true);
	if (rtn.substring(0, 2).equals("ok")) {
	    lastSQLID = Integer.parseInt(rtn.substring(3));
	} else {
	    // error
	    lastSQLID = ERROR;
	}
	return lastSQLID;
    }

    private String processData(String data, boolean newEntry)
	    throws SQLException {
	if (mode == Database.PHP_MODE) {
	    // PHP MODE
	    String script = phpDetails.path;
	    if (newEntry) {
		script = script + phpDetails.newDataScript + "?" + "dummy";
		script = script + data;
	    } else {
		script = script + phpDetails.updateDataScript + "?";
		script = script + sqlIDName + "=" + lastSQLID;
		script = script + data;
	    }
	    return runPhpScript(script);
	} else if (mode == Database.FILE_MODE) {
	    // FILE MODE
	    if (fileName == "") {
		newEntry = true;
	    }
	    if (newEntry) { // 
		// Date now = new Date();
		// String dateStr =
		// DateFormat.getDateTimeInstance(DateFormat.SHORT,
		// DateFormat.SHORT).format(now);
		// data = "\n\n**** new data " + dateStr +"****\n" + data;
		// dateStr =
		// DateFormat.getDateInstance(DateFormat.SHORT).format(now);
		// dateStr= dateStr.replaceAll("/","");
		// dateStr= dateStr.replaceAll("\\.","");
		// dateStr= dateStr.replaceAll("-","");
		fileName = appletName + "-" + tag + "-data.csv";
		dataLine = "";
	    }
	    return writeDataLineForFile(data);
	}
	if (mode == Database.SQL_MODE) {
	    // SQL MODE
	    ArrayList<String> split = separateVarFromValsForSQL(data);
	    if (newEntry) {
		// insert
		int id = sqlHdl
			.insertInto(sqlTable, split.get(0), split.get(1));
		return "ok " + id;
	    } else {
		// update
		String sqlUpdate = prepareFromSQLUpdate(data);
		String where = "`" + sqlIDName + "`='" + lastSQLID + "'";
		sqlHdl.update(sqlTable, sqlUpdate, where);
		return "not implementet yet";
	    }

	} else {
	    return "Please set mode!";
	}
    }

    public String removeSpecialChars(String str) {
	if (str == null) {
	    return null;
	}
	if (mode == Database.PHP_MODE) {
	    str = str.replaceAll(" ", "%20");
	    str = str.replaceAll("<", "_");
	    str = str.replaceAll(">", "_");
	    str = str.replaceAll("&", "%26");
	}
	return str;
    }

    public void saveDataLineToDisk() {
	if (mode == Database.FILE_MODE) {
	    boolean exists = (new File(fileName)).exists();
	    if (!exists) {
		String varnames = "date,startTime,initials,gender,year,mtongue,lang,country,f01,f02,f03,f04,f05,f06,f07,f08,f09,f10,,t01,t02,t03,t04,t05,t06,t07,t08,t09,t10,,a01,a02,a03,a04,a05,a06,a07,a08,a09,a10,a11,a12,a13";
		dataLine = varnames + "\n" + dataLine;
	    }
	    ;

	    try {
		BufferedWriter writer = new BufferedWriter(new FileWriter(
			fileName, true));
		writer.write(dataLine);
		writer.newLine();
		writer.close();
	    } catch (IOException e) {
		e.printStackTrace();
	    }
	    dataLine = "";
	}
    }

    public void setFileMode() {
	phpDetails = null;
	fileName = "";
	sqlIDName = "";
	lastSQLID = ERROR;
	mode = FILE_MODE;
    }

    public void setPHPMode(PHPDetails thePHPDetails, String sqlIDName) {
	phpDetails = thePHPDetails;
	this.sqlIDName = sqlIDName;
	lastSQLID = ERROR;
	mode = PHP_MODE;
    }

    public boolean setSQLMode(SQLHandler.ConnectionDetails theSQLDetails,
	    String sqlTable, String sqlIDName) {
	// connect SQL
	if (sqlHdl == null) {
	    sqlHdl = new SQLHandler();
	}
	sqlHdl.setConnectionDetails(theSQLDetails);
	try {
	    sqlHdl.connect();
	} catch (ClassNotFoundException e) {
	    // Auto-generated catch block
	    e.printStackTrace();
	    return false;
	} catch (SQLException e) {
	    // Auto-generated catch block
	    e.printStackTrace();
	    return false;
	}

	this.sqlIDName = sqlIDName;
	this.sqlTable = sqlTable;
	phpDetails = null;
	lastSQLID = ERROR;
	mode = SQL_MODE;

	return true;
    }

    private String writeDataLineForFile(String data) {
	data = data.replaceAll("\n", "");
	String[] lines = data.split("&");
	for (int i = 0; i < lines.length; i++) {
	    if (lines[i].contains("date") || lines[i].contains("startTime")
		    || lines[i].contains("initials")
		    || lines[i].contains("gender") || lines[i].contains("year")
		    || lines[i].contains("mtongue")
		    || lines[i].contains("lang")
		    || lines[i].contains("country")
		    || lines[i].contains("fngSq") || lines[i].contains("times")
		    || lines[i].contains("answers")) {
		String[] parts = lines[i].split("=");
		if (parts.length >= 2) {
		    dataLine = dataLine + parts[1];
		}
		dataLine = dataLine + ',';
	    }// if contains
	}
	return "ok -1";
    }

    /*
     * public void SQLconnect(String theServer, String theDatabase, String
     * theUser, String thePassword){ }
     */

}
